DBMS SQL Queries with Answers

Sample Table:
Employee(EmpID, Name, Department, Salary)

━━━━━━━━━━━━━━━━━━━━

*1. Find the Highest Salary*

Question:
Find the employee(s) having the highest salary.

Query:
SELECT *
FROM Employee
WHERE Salary = (
    SELECT MAX(Salary)
    FROM Employee
);

Explanation:
• MAX(Salary) finds the highest salary.
• The outer query returns the employee(s) having that salary.

━━━━━━━━━━━━━━━━━━━━

*2. Find the Second Highest Salary*

Method 1 (Most Asked)

SELECT MAX(Salary)
FROM Employee
WHERE Salary < (
    SELECT MAX(Salary)
    FROM Employee
);

Explanation:
• First MAX() finds the highest salary.
• Second MAX() finds the next highest salary.

━━━━━━━━━━━━━━━━━━━━

Method 2 (Using LIMIT)

SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1;

Used mainly in MySQL.

━━━━━━━━━━━━━━━━━━━━

*3. Find the Nth Highest Salary*

Example: 3rd Highest Salary

SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 2;

General Formula:

OFFSET = N - 1

Examples:
2nd Highest → OFFSET 1
3rd Highest → OFFSET 2
4th Highest → OFFSET 3

━━━━━━━━━━━━━━━━━━━━

*4. Find Duplicate Records*

Find duplicate employee names.

SELECT Name, COUNT(*)
FROM Employee
GROUP BY Name
HAVING COUNT(*) > 1;

Explanation:
• GROUP BY groups identical names.
• HAVING filters only duplicates.

━━━━━━━━━━━━━━━━━━━━

*5. Delete Duplicate Records*

Assume EmpID is unique.

DELETE e1
FROM Employee e1
JOIN Employee e2
ON e1.Name = e2.Name
AND e1.EmpID > e2.EmpID;

Explanation:
• Keeps one record.
• Deletes remaining duplicates.

━━━━━━━━━━━━━━━━━━━━

*6. Count Total Employees*

SELECT COUNT(*)
FROM Employee;

Returns the total number of employees.

━━━━━━━━━━━━━━━━━━━━

*7. Count Employees Department-wise*

SELECT Department,
COUNT(*) AS TotalEmployees
FROM Employee
GROUP BY Department;

Example Output

CSE → 25

ECE → 18

IT → 20

━━━━━━━━━━━━━━━━━━━━

*8. Maximum Salary Department-wise*

SELECT Department,
MAX(Salary) AS HighestSalary
FROM Employee
GROUP BY Department;

Example Output

HR → ₹70,000

IT → ₹90,000

Finance → ₹80,000

━━━━━━━━━━━━━━━━━━━━

*9. Average Salary*

Overall Average Salary

SELECT AVG(Salary)
FROM Employee;

Department-wise Average Salary

SELECT Department,
AVG(Salary)
FROM Employee
GROUP BY Department;

━━━━━━━━━━━━━━━━━━━━

*10. Students Above Average Marks*

Sample Table:
Student(ID, Name, Marks)

SELECT *
FROM Student
WHERE Marks >
(
SELECT AVG(Marks)
FROM Student
);

Explanation:
• AVG(Marks) calculates average marks.
• Displays students scoring above average.

━━━━━━━━━━━━━━━━━━━━

*11. Find Minimum Salary*

SELECT MIN(Salary)
FROM Employee;

Returns the lowest salary.

━━━━━━━━━━━━━━━━━━━━

*12. Find Employees Earning More Than ₹50,000*

SELECT *
FROM Employee
WHERE Salary > 50000;

━━━━━━━━━━━━━━━━━━━━

*13. Find Employees Belonging to IT Department*

SELECT *
FROM Employee
WHERE Department = 'IT';

━━━━━━━━━━━━━━━━━━━━

*14. Display Employees in Ascending Salary Order*

SELECT *
FROM Employee
ORDER BY Salary ASC;

Descending Order

SELECT *
FROM Employee
ORDER BY Salary DESC;

━━━━━━━━━━━━━━━━━━━━

*15. Display Top 5 Highest Paid Employees*

SELECT *
FROM Employee
ORDER BY Salary DESC
LIMIT 5;

(MySQL)

━━━━━━━━━━━━━━━━━━━━

*Frequently Asked SQL Functions*

COUNT() → Counts records

SUM() → Total

AVG() → Average

MAX() → Highest value

MIN() → Lowest value

━━━━━━━━━━━━━━━━━━━━

*16. Find Employees Whose Name Starts with 'A'*

SELECT *
FROM Employee
WHERE Name LIKE 'A%';

Explanation:
• % represents any number of characters.
• Returns names starting with A.

━━━━━━━━━━━━━━━━━━━━

*17. Find Employees Whose Name Ends with 'N'*

SELECT *
FROM Employee
WHERE Name LIKE '%N';

━━━━━━━━━━━━━━━━━━━━

*18. Find Employees Whose Name Contains 'RA'*

SELECT *
FROM Employee
WHERE Name LIKE '%RA%';

━━━━━━━━━━━━━━━━━━━━

*19. Find Employees Having Salary Between ₹40,000 and ₹80,000*

SELECT *
FROM Employee
WHERE Salary BETWEEN 40000 AND 80000;

Explanation:
BETWEEN includes both boundary values.

━━━━━━━━━━━━━━━━━━━━

*20. Find Employees Working in Multiple Departments*

SELECT DISTINCT Department
FROM Employee;

Explanation:
DISTINCT removes duplicate department names.

━━━━━━━━━━━━━━━━━━━━

*21. Find Total Salary of All Employees*

SELECT SUM(Salary)
FROM Employee;

Returns the sum of all salaries.

━━━━━━━━━━━━━━━━━━━━

*22. Find Number of Employees in IT Department*

SELECT COUNT(*)
FROM Employee
WHERE Department='IT';

━━━━━━━━━━━━━━━━━━━━

*23. Find Employees Without Department*

SELECT *
FROM Employee
WHERE Department IS NULL;

Explanation:
NULL values must be checked using IS NULL.

━━━━━━━━━━━━━━━━━━━━

*24. Find Employees With Department Assigned*

SELECT *
FROM Employee
WHERE Department IS NOT NULL;

━━━━━━━━━━━━━━━━━━━━

*25. Display Unique Departments*

SELECT DISTINCT Department
FROM Employee;

━━━━━━━━━━━━━━━━━━━━

*26. Find Highest Paid Employee in Each Department*

SELECT Department,
MAX(Salary) AS HighestSalary
FROM Employee
GROUP BY Department;

━━━━━━━━━━━━━━━━━━━━

*27. Find Lowest Paid Employee in Each Department*

SELECT Department,
MIN(Salary) AS LowestSalary
FROM Employee
GROUP BY Department;

━━━━━━━━━━━━━━━━━━━━

*28. Find Departments Having More Than 5 Employees*

SELECT Department,
COUNT(*)
FROM Employee
GROUP BY Department
HAVING COUNT(*) > 5;

Explanation:
HAVING filters grouped results.

━━━━━━━━━━━━━━━━━━━━

*29. Find Employees Whose Salary is Greater Than Department Average*

SELECT *
FROM Employee e
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employee
WHERE Department=e.Department
);

Explanation:
This is a Correlated Subquery.
Very commonly asked in Digital interviews.

━━━━━━━━━━━━━━━━━━━━

*30. Find Employees Who Earn the Same Salary*

SELECT Salary,
COUNT(*)
FROM Employee
GROUP BY Salary
HAVING COUNT(*) > 1;

━━━━━━━━━━━━━━━━━━━━

*31. Difference Between WHERE and HAVING*

WHERE
• Filters rows
• Before GROUP BY
• Aggregate functions not allowed

HAVING
• Filters groups
• After GROUP BY
• Aggregate functions allowed

━━━━━━━━━━━━━━━━━━━━

*32. Difference Between GROUP BY and ORDER BY*

GROUP BY
• Groups similar records
• Used with Aggregate Functions

ORDER BY
• Sorts records
• ASC or DESC

━━━━━━━━━━━━━━━━━━━━

*33. Difference Between UNION and UNION ALL*

UNION
• Removes duplicates
• Slower

UNION ALL
• Keeps duplicates
• Faster

━━━━━━━━━━━━━━━━━━━━

*34. Difference Between DELETE, DROP and TRUNCATE*

DELETE
• Removes selected rows
• WHERE allowed
• Rollback possible

TRUNCATE
• Removes all rows
• Faster
• WHERE not allowed

DROP
• Deletes entire table
• Table structure removed

━━━━━━━━━━━━━━━━━━━━

*35. Difference Between CHAR and VARCHAR*

CHAR
• Fixed Length
• Faster
• Wastes space

VARCHAR
• Variable Length
• Saves memory
• Mostly used in applications

━━━━━━━━━━━━━━━━━━━━

*36. Difference Between Primary Key and Unique Key*

Primary Key
• Unique
• Cannot be NULL
• One per table

Unique Key
• Unique
• Can contain one NULL (DBMS dependent)
• Multiple allowed

━━━━━━━━━━━━━━━━━━━━

*37. Difference Between Primary Key and Foreign Key*

Primary Key
• Identifies records
• Unique
• Cannot be NULL

Foreign Key
• Creates relationship
• Can repeat
• Can be NULL

━━━━━━━━━━━━━━━━━━━━

*Most Important SQL Clauses*

SELECT → Retrieve Data

FROM → Source Table

WHERE → Filter Rows

GROUP BY → Group Records

HAVING → Filter Groups

ORDER BY → Sort Data

LIMIT → Restrict Output
━━━━━━━━━━━━━━━━━━━━

*Interview Tip*

If the interviewer asks you to write a SQL query:

1. Read the question carefully.
2. Identify the table and required columns.
3. Decide whether you need:
   • WHERE
   • GROUP BY
   • HAVING
   • ORDER BY
   • JOIN
   • Aggregate Functions
4. Write the query confidently.
5. Explain your logic after writing it.